Оглавление

• Анализ приложения Procrastinate Pro+
  • Шаг 1. Загрузим данные и подготовим их к анализу
    • • Импортируем библиотеки и настроим Python
      • Загрузим данные о визитах, заказах и рекламных расходах из CSV-файлов в переменные
      • Изучим данные
      • Приведем к "змеиному" регистру названия столбцов
      • Изменим типы данных в столбцах с датами на datetime
      • Проверим наличие дублирующихся строк
  • Шаг 2. Зададим функции для расчёта и анализа LTV, ROI, удержания и конверсии.
  • Шаг 3. Исследовательский анализ данных
    • • Составим профили пользователей. Определим минимальную и максимальную даты привлечения пользователей.
      • Выясним, из каких стран пользователи приходят в приложение и на какую страну приходится больше всего платящих пользователей. Построим таблицу, отражающую количество пользователей и долю платящих из каждой страны.
      • Проанализируем, какими устройствами пользуются клиенты и какие устройства предпочитают платящие пользователи. Построим таблицу, отражающую количество пользователей и долю платящих для каждого устройства.
      • Изучим рекламные источники привлечения и определите канам, из которых пришло больше всего платящих пользователей. Построим таблицу, отражающую количество пользователей и долю платящих для каждого канала привлечения.
  • Шаг 4. Маркетинг
    • • Посчитаем общую сумму расходов на маркетинг, проанализируем его распределение по рекламным источникам
      • Построим визуализацию динамики изменения общих расходов во времени (по неделям и месяцам) по каждому источнику
      • Узнаем, сколько в среднем стоило привлечение одного пользователя (CAC) из каждого источника
  • Шаг 5. Оценим окупаемость рекламы
    • • Исключим органических пользователей при анализе окупаемости рекламы
      • Используя графики LTV, ROI и CAC, проанализируем окупаемость рекламы
      • Проверим конверсию и удержание пользователей и динамику её изменения
      • Проанализируем окупаемость рекламы с разбивкой по устройствам. Построим графики LTV и ROI, а также графики динамики LTV, CAC и ROI.
      • Проанализируем окупаемость рекламы с разбивкой по странам. Построим графики LTV и ROI, а также графики динамики LTV, CAC и ROI.
      • Проанализируем окупаемость рекламы с разбивкой по рекламным каналам. Построим графики LTV и ROI, а также графики динамики LTV, CAC и ROI.
      • Проанализируем конверсию и удержание пользователей в разбивке по каналам привлечения
  • Выводы

Анализ приложения Procrastinate Pro+

Проведем маркетинговый анализ развлекательного приложения Procrastinate Pro+. Несмотря на огромные вложения в рекламу, последние несколько месяцев компания терпит убытки. Задача — разобраться в причинах и помочь компании выйти в плюс.

Есть данные о пользователях, привлечённых с 1 мая по 27 октября 2019 года:

• лог сервера с данными об их посещениях,
• выгрузка их покупок за этот период,
• рекламные расходы.

Необходимо проанализировать:

• откуда приходят пользователи и какими устройствами они пользуются,
• сколько стоит привлечение пользователей из различных рекламных каналов;
• сколько денег приносит каждый клиент,
• когда расходы на привлечение клиента окупаются,
• какие факторы мешают привлечению клиентов.

Шаг 1. Загрузим данные и подготовим их к анализу

Импортируем библиотеки и настроим Python

# загрузка необходимых библиотек
import pandas as pd
import seaborn as sns
from datetime import datetime, timedelta
from matplotlib import pyplot as plt
import numpy as np

# убираем предупреждения
pd.options.mode.chained_assignment = None  # default='warn'
from matplotlib.axes._axes import _log as matplotlib_axes_logger
matplotlib_axes_logger.setLevel('ERROR')

# увеличим максимальное количество отображающихся столбцов
pd.set_option('display.max_columns', None)

Загрузим данные о визитах, заказах и рекламных расходах из CSV-файлов в переменные

# загружаем данные
try:
    visits = pd.read_csv('visits_info_short.csv')
    orders = pd.read_csv('orders_info_short.csv')
    costs = pd.read_csv('costs_info_short.csv')
except:
    visits = pd.read_csv('/datasets/visits_info_short.csv')
    orders = pd.read_csv('/datasets/orders_info_short.csv')
    costs = pd.read_csv('/datasets/costs_info_short.csv')

Изучим данные

print('visits')
print(visits.info())
print()
print('orders')
print(orders.info())
print()
print('costs')
print(costs.info())

visits
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 309901 entries, 0 to 309900
Data columns (total 6 columns):
 #   Column         Non-Null Count   Dtype 
---  ------         --------------   ----- 
 0   User Id        309901 non-null  int64 
 1   Region         309901 non-null  object
 2   Device         309901 non-null  object
 3   Channel        309901 non-null  object
 4   Session Start  309901 non-null  object
 5   Session End    309901 non-null  object
dtypes: int64(1), object(5)
memory usage: 14.2+ MB
None

orders
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 40212 entries, 0 to 40211
Data columns (total 3 columns):
 #   Column    Non-Null Count  Dtype  
---  ------    --------------  -----  
 0   User Id   40212 non-null  int64  
 1   Event Dt  40212 non-null  object 
 2   Revenue   40212 non-null  float64
dtypes: float64(1), int64(1), object(1)
memory usage: 942.6+ KB
None

costs
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1800 entries, 0 to 1799
Data columns (total 3 columns):
 #   Column   Non-Null Count  Dtype  
---  ------   --------------  -----  
 0   dt       1800 non-null   object 
 1   Channel  1800 non-null   object 
 2   costs    1800 non-null   float64
dtypes: float64(1), object(2)
memory usage: 42.3+ KB
None

*Выводы*
1) во всех трех таблицах отсутствуют пропуски
2.1) данные в столбцах Session Start и Session End датафрейма visits необходимо перевести в формат datetime
2.2) данные в столбце Event Dtдатафрейма orders необходимо перевести в формат datetime
2.3) данные в столбце dt датафрейма costs необходимо перевести в формат datetime
3) названия столбцов необходимо перевести в "змеиный" регистр
4) данные необходимо проверить на наличие дублирующихся строк

Приведем к "змеиному" регистру названия столбцов

# переведем названия на нижний регистр
visits.columns = visits.columns.str.lower()
orders.columns = orders.columns.str.lower()
costs.columns = costs.columns.str.lower()

# заменим "пробел" в названии столбца на нижнее подчеркивание - "_" 
visits.columns = visits.columns.str.replace(' ', '_')
orders.columns = orders.columns.str.replace(' ', '_')
costs.columns = costs.columns.str.replace(' ', '_')

Изменим типы данных в столбцах с датами на datetime

visits['session_end'] = pd.to_datetime(visits['session_end'])
visits['session_start'] = pd.to_datetime(visits['session_start'])

orders['event_dt'] = pd.to_datetime(orders['event_dt'])

costs['dt'] = pd.to_datetime(costs['dt'])

Проверим наличие дублирующихся строк

visits[visits.duplicated()]

	user_id	region	device	channel	session_start	session_end

orders[orders.duplicated()]

	user_id	event_dt	revenue

costs[costs.duplicated()]

	dt	channel	costs

Вывод: строки-дубликаты отсутствуют во всех датафреймах.

Шаг 2. Зададим функции для расчёта и анализа LTV, ROI, удержания и конверсии.

Функции для вычисления значений метрик:

• get_profiles() — для создания профилей пользователей,
• get_retention() — для подсчёта Retention Rate,
• get_conversion() — для подсчёта конверсии,
• get_ltv() — для подсчёта LTV.

А также функции для построения графиков:

• filter_data() — для сглаживания данных,
• plot_retention() — для построения графика Retention Rate,
• plot_conversion() — для построения графика конверсии,
• plot_ltv_roi — для визуализации LTV и ROI.

# функция для создания пользовательских профилей

def get_profiles(sessions, orders, ad_costs):

    # находим параметры первых посещений
    profiles = (
        sessions.sort_values(by=['user_id', 'session_start'])
        .groupby('user_id')
        .agg(
            {
                'session_start': 'first',
                'channel': 'first',
                'device': 'first',
                'region': 'first',
            }
        )
        .rename(columns={'session_start': 'first_ts'})
        .reset_index()
    )

    # для когортного анализа определяем дату первого посещения
    # и первый день месяца, в который это посещение произошло
    profiles['dt'] = profiles['first_ts'].dt.date
    profiles['month'] = profiles['first_ts'].astype('datetime64[M]')

    # добавляем признак платящих пользователей
    profiles['payer'] = profiles['user_id'].isin(orders['user_id'].unique())

    # считаем количество уникальных пользователей
    # с одинаковыми источником и датой привлечения
    new_users = (
        profiles.groupby(['dt', 'channel'])
        .agg({'user_id': 'nunique'})
        .rename(columns={'user_id': 'unique_users'})
        .reset_index()
    )

    # объединяем траты на рекламу и число привлечённых пользователей
    ad_costs['dt'] = pd.to_datetime(ad_costs['dt']).dt.date
    ad_costs = ad_costs.merge(new_users, on=['dt', 'channel'], how='left')

    # делим рекламные расходы на число привлечённых пользователей
    ad_costs['acquisition_cost'] = ad_costs['costs'] / ad_costs['unique_users']

    # добавляем стоимость привлечения в профили
    profiles = profiles.merge(
        ad_costs[['dt', 'channel', 'acquisition_cost']],
        on=['dt', 'channel'],
        how='left',
    )

    # стоимость привлечения органических пользователей равна нулю
    profiles['acquisition_cost'] = profiles['acquisition_cost'].fillna(0)

    return profiles

# функция для расчёта удержания

def get_retention(
    profiles,
    sessions,
    observation_date,
    horizon_days,
    dimensions=[],
    ignore_horizon=False,
):

    # добавляем столбец payer в передаваемый dimensions список
    dimensions = ['payer'] + dimensions

    # исключаем пользователей, не «доживших» до горизонта анализа
    last_suitable_acquisition_date = observation_date
    if not ignore_horizon:
        last_suitable_acquisition_date = observation_date - timedelta(
            days=horizon_days - 1
        )
    result_raw = profiles.query('dt <= @last_suitable_acquisition_date')

    # собираем «сырые» данные для расчёта удержания
    result_raw = result_raw.merge(
        sessions[['user_id', 'session_start']], on='user_id', how='left'
    )
    result_raw['lifetime'] = (
        result_raw['session_start'] - result_raw['first_ts']
    ).dt.days

    # функция для группировки таблицы по желаемым признакам
    def group_by_dimensions(df, dims, horizon_days):
        result = df.pivot_table(
            index=dims, columns='lifetime', values='user_id', aggfunc='nunique'
        )
        cohort_sizes = (
            df.groupby(dims)
            .agg({'user_id': 'nunique'})
            .rename(columns={'user_id': 'cohort_size'})
        )
        result = cohort_sizes.merge(result, on=dims, how='left').fillna(0)
        result = result.div(result['cohort_size'], axis=0)
        result = result[['cohort_size'] + list(range(horizon_days))]
        result['cohort_size'] = cohort_sizes
        return result

    # получаем таблицу удержания
    result_grouped = group_by_dimensions(result_raw, dimensions, horizon_days)

    # получаем таблицу динамики удержания
    result_in_time = group_by_dimensions(
        result_raw, dimensions + ['dt'], horizon_days
    )

    # возвращаем обе таблицы и сырые данные
    return result_raw, result_grouped, result_in_time

# функция для расчёта конверсии

def get_conversion(
    profiles,
    purchases,
    observation_date,
    horizon_days,
    dimensions=[],
    ignore_horizon=False,
):

    # исключаем пользователей, не «доживших» до горизонта анализа
    last_suitable_acquisition_date = observation_date
    if not ignore_horizon:
        last_suitable_acquisition_date = observation_date - timedelta(
            days=horizon_days - 1
        )
    result_raw = profiles.query('dt <= @last_suitable_acquisition_date')

    # определяем дату и время первой покупки для каждого пользователя
    first_purchases = (
        purchases.sort_values(by=['user_id', 'event_dt'])
        .groupby('user_id')
        .agg({'event_dt': 'first'})
        .reset_index()
    )

    # добавляем данные о покупках в профили
    result_raw = result_raw.merge(
        first_purchases[['user_id', 'event_dt']], on='user_id', how='left'
    )

    # рассчитываем лайфтайм для каждой покупки
    result_raw['lifetime'] = (
        result_raw['event_dt'] - result_raw['first_ts']
    ).dt.days

    # группируем по cohort, если в dimensions ничего нет
    if len(dimensions) == 0:
        result_raw['cohort'] = 'All users' 
        dimensions = dimensions + ['cohort']

    # функция для группировки таблицы по желаемым признакам
    def group_by_dimensions(df, dims, horizon_days):
        result = df.pivot_table(
            index=dims, columns='lifetime', values='user_id', aggfunc='nunique'
        )
        result = result.fillna(0).cumsum(axis = 1)
        cohort_sizes = (
            df.groupby(dims)
            .agg({'user_id': 'nunique'})
            .rename(columns={'user_id': 'cohort_size'})
        )
        result = cohort_sizes.merge(result, on=dims, how='left').fillna(0)
        # делим каждую «ячейку» в строке на размер когорты
        # и получаем conversion rate
        result = result.div(result['cohort_size'], axis=0)
        result = result[['cohort_size'] + list(range(horizon_days))]
        result['cohort_size'] = cohort_sizes
        return result

    # получаем таблицу конверсии
    result_grouped = group_by_dimensions(result_raw, dimensions, horizon_days)

    # для таблицы динамики конверсии убираем 'cohort' из dimensions
    if 'cohort' in dimensions: 
        dimensions = []

    # получаем таблицу динамики конверсии
    result_in_time = group_by_dimensions(
        result_raw, dimensions + ['dt'], horizon_days
    )

    # возвращаем обе таблицы и сырые данные
    return result_raw, result_grouped, result_in_time

# функция для расчёта LTV и ROI

def get_ltv(
    profiles,
    purchases,
    observation_date,
    horizon_days,
    dimensions=[],
    ignore_horizon=False,
):

    # исключаем пользователей, не «доживших» до горизонта анализа
    last_suitable_acquisition_date = observation_date
    if not ignore_horizon:
        last_suitable_acquisition_date = observation_date - timedelta(
            days=horizon_days - 1
        )
    result_raw = profiles.query('dt <= @last_suitable_acquisition_date')
    # добавляем данные о покупках в профили
    result_raw = result_raw.merge(
        purchases[['user_id', 'event_dt', 'revenue']], on='user_id', how='left'
    )
    # рассчитываем лайфтайм пользователя для каждой покупки
    result_raw['lifetime'] = (
        result_raw['event_dt'] - result_raw['first_ts']
    ).dt.days
    # группируем по cohort, если в dimensions ничего нет
    if len(dimensions) == 0:
        result_raw['cohort'] = 'All users'
        dimensions = dimensions + ['cohort']

    # функция группировки по желаемым признакам
    def group_by_dimensions(df, dims, horizon_days):
        # строим «треугольную» таблицу выручки
        result = df.pivot_table(
            index=dims, columns='lifetime', values='revenue', aggfunc='sum'
        )
        # находим сумму выручки с накоплением
        result = result.fillna(0).cumsum(axis=1)
        # вычисляем размеры когорт
        cohort_sizes = (
            df.groupby(dims)
            .agg({'user_id': 'nunique'})
            .rename(columns={'user_id': 'cohort_size'})
        )
        # объединяем размеры когорт и таблицу выручки
        result = cohort_sizes.merge(result, on=dims, how='left').fillna(0)
        # считаем LTV: делим каждую «ячейку» в строке на размер когорты
        result = result.div(result['cohort_size'], axis=0)
        # исключаем все лайфтаймы, превышающие горизонт анализа
        result = result[['cohort_size'] + list(range(horizon_days))]
        # восстанавливаем размеры когорт
        result['cohort_size'] = cohort_sizes

        # собираем датафрейм с данными пользователей и значениями CAC, 
        # добавляя параметры из dimensions
        cac = df[['user_id', 'acquisition_cost'] + dims].drop_duplicates()

        # считаем средний CAC по параметрам из dimensions
        cac = (
            cac.groupby(dims)
            .agg({'acquisition_cost': 'mean'})
            .rename(columns={'acquisition_cost': 'cac'})
        )

        # считаем ROI: делим LTV на CAC
        roi = result.div(cac['cac'], axis=0)

        # удаляем строки с бесконечным ROI
        roi = roi[~roi['cohort_size'].isin([np.inf])]

        # восстанавливаем размеры когорт в таблице ROI
        roi['cohort_size'] = cohort_sizes

        # добавляем CAC в таблицу ROI
        roi['cac'] = cac['cac']

        # в финальной таблице оставляем размеры когорт, CAC
        # и ROI в лайфтаймы, не превышающие горизонт анализа
        roi = roi[['cohort_size', 'cac'] + list(range(horizon_days))]

        # возвращаем таблицы LTV и ROI
        return result, roi

    # получаем таблицы LTV и ROI
    result_grouped, roi_grouped = group_by_dimensions(
        result_raw, dimensions, horizon_days
    )

    # для таблиц динамики убираем 'cohort' из dimensions
    if 'cohort' in dimensions:
        dimensions = []

    # получаем таблицы динамики LTV и ROI
    result_in_time, roi_in_time = group_by_dimensions(
        result_raw, dimensions + ['dt'], horizon_days
    )

    return (
        result_raw,  # сырые данные
        result_grouped,  # таблица LTV
        result_in_time,  # таблица динамики LTV
        roi_grouped,  # таблица ROI
        roi_in_time,  # таблица динамики ROI
    )

# функция для сглаживания фрейма

def filter_data(df, window):
    # для каждого столбца применяем скользящее среднее
    for column in df.columns.values:
        df[column] = df[column].rolling(window).mean() 
    return df

# функция для визуализации удержания

def plot_retention(retention, retention_history, horizon, window=7):

    # задаём размер сетки для графиков
    plt.figure(figsize=(15, 10))

    # исключаем размеры когорт и удержание первого дня
    retention = retention.drop(columns=['cohort_size', 0])
    # в таблице динамики оставляем только нужный лайфтайм
    retention_history = retention_history.drop(columns=['cohort_size'])[
        [horizon - 1]
    ]

    # если в индексах таблицы удержания только payer,
    # добавляем второй признак — cohort
    if retention.index.nlevels == 1:
        retention['cohort'] = 'All users'
        retention = retention.reset_index().set_index(['cohort', 'payer'])

    # в таблице графиков — два столбца и две строки, четыре ячейки
    # в первой строим кривые удержания платящих пользователей
    ax1 = plt.subplot(2, 2, 1)
    retention.query('payer == True').droplevel('payer').T.plot(
        grid=True, ax=ax1
    )
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('Удержание платящих пользователей')

    # во второй ячейке строим кривые удержания неплатящих
    # вертикальная ось — от графика из первой ячейки
    ax2 = plt.subplot(2, 2, 2, sharey=ax1)
    retention.query('payer == False').droplevel('payer').T.plot(
        grid=True, ax=ax2
    )
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('Удержание неплатящих пользователей')

    # в третьей ячейке — динамика удержания платящих
    ax3 = plt.subplot(2, 2, 3)
    # получаем названия столбцов для сводной таблицы
    columns = [
        name
        for name in retention_history.index.names
        if name not in ['dt', 'payer']
    ]
    # фильтруем данные и строим график
    filtered_data = retention_history.query('payer == True').pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax3)
    plt.xlabel('Дата привлечения')
    plt.title(
        'Динамика удержания платящих пользователей на {}-й день'.format(
            horizon
        )
    )

    # в чётвертой ячейке — динамика удержания неплатящих
    ax4 = plt.subplot(2, 2, 4, sharey=ax3)
    # фильтруем данные и строим график
    filtered_data = retention_history.query('payer == False').pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax4)
    plt.xlabel('Дата привлечения')
    plt.title(
        'Динамика удержания неплатящих пользователей на {}-й день'.format(
            horizon
        )
    )
    
    plt.tight_layout()
    plt.show()

# функция для визуализации конверсии

def plot_conversion(conversion, conversion_history, horizon, window=7):

    # задаём размер сетки для графиков
    plt.figure(figsize=(15, 5))

    # исключаем размеры когорт
    conversion = conversion.drop(columns=['cohort_size'])
    # в таблице динамики оставляем только нужный лайфтайм
    conversion_history = conversion_history.drop(columns=['cohort_size'])[
        [horizon - 1]
    ]

    # первый график — кривые конверсии
    ax1 = plt.subplot(1, 2, 1)
    conversion.T.plot(grid=True, ax=ax1)
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('Конверсия пользователей')

    # второй график — динамика конверсии
    ax2 = plt.subplot(1, 2, 2, sharey=ax1)
    columns = [
        # столбцами сводной таблицы станут все столбцы индекса, кроме даты
        name for name in conversion_history.index.names if name not in ['dt']
    ]
    filtered_data = conversion_history.pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax2)
    plt.xlabel('Дата привлечения')
    plt.title('Динамика конверсии пользователей на {}-й день'.format(horizon))

    plt.tight_layout()
    plt.show()

# функция для визуализации LTV и ROI

def plot_ltv_roi(ltv, ltv_history, roi, roi_history, horizon, window=7):

    # задаём сетку отрисовки графиков
    plt.figure(figsize=(20, 10))

    # из таблицы ltv исключаем размеры когорт
    ltv = ltv.drop(columns=['cohort_size'])
    # в таблице динамики ltv оставляем только нужный лайфтайм
    ltv_history = ltv_history.drop(columns=['cohort_size'])[[horizon - 1]]

    # стоимость привлечения запишем в отдельный фрейм
    cac_history = roi_history[['cac']]

    # из таблицы roi исключаем размеры когорт и cac
    roi = roi.drop(columns=['cohort_size', 'cac'])
    # в таблице динамики roi оставляем только нужный лайфтайм
    roi_history = roi_history.drop(columns=['cohort_size', 'cac'])[
        [horizon - 1]
    ]

    # первый график — кривые ltv
    ax1 = plt.subplot(2, 3, 1)
    ltv.T.plot(grid=True, ax=ax1)
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('LTV')

    # второй график — динамика ltv
    ax2 = plt.subplot(2, 3, 2, sharey=ax1)
    # столбцами сводной таблицы станут все столбцы индекса, кроме даты
    columns = [name for name in ltv_history.index.names if name not in ['dt']]
    filtered_data = ltv_history.pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax2)
    plt.xlabel('Дата привлечения')
    plt.title('Динамика LTV пользователей на {}-й день'.format(horizon))

    # третий график — динамика cac
    ax3 = plt.subplot(2, 3, 3, sharey=ax1)
    # столбцами сводной таблицы станут все столбцы индекса, кроме даты
    columns = [name for name in cac_history.index.names if name not in ['dt']]
    filtered_data = cac_history.pivot_table(
        index='dt', columns=columns, values='cac', aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax3)
    plt.xlabel('Дата привлечения')
    plt.title('Динамика стоимости привлечения пользователей')

    # четвёртый график — кривые roi
    ax4 = plt.subplot(2, 3, 4)
    roi.T.plot(grid=True, ax=ax4)
    plt.axhline(y=1, color='red', linestyle='--', label='Уровень окупаемости')
    plt.legend()
    plt.xlabel('Лайфтайм')
    plt.title('ROI')

    # пятый график — динамика roi
    ax5 = plt.subplot(2, 3, 5, sharey=ax4)
    # столбцами сводной таблицы станут все столбцы индекса, кроме даты
    columns = [name for name in roi_history.index.names if name not in ['dt']]
    filtered_data = roi_history.pivot_table(
        index='dt', columns=columns, values=horizon - 1, aggfunc='mean'
    )
    filter_data(filtered_data, window).plot(grid=True, ax=ax5)
    plt.axhline(y=1, color='red', linestyle='--', label='Уровень окупаемости')
    plt.xlabel('Дата привлечения')
    plt.title('Динамика ROI пользователей на {}-й день'.format(horizon))

    plt.tight_layout()
    plt.show()

Шаг 3. Исследовательский анализ данных

Составим профили пользователей. Определим минимальную и максимальную даты привлечения пользователей.

Профили пользователей получим с помощью функции get_profiles().

Установим момент и горизонт анализа данных. У нас имеются данные о пользователях, привлечённых с 1 мая по 27 октября 2019 года. Следовательно, установим момент анализа на 27 октября 2019 года, и зададим недельный горизонт анализа.

observation_date = datetime(2019, 10, 27).date()  # момент анализа
horizon_days = 7  # горизонт анализа 

# получаем профили пользователей
profiles = get_profiles(visits, orders, costs)
profiles

	user_id	first_ts	channel	device	region	dt	month	payer	acquisition_cost
0	599326	2019-05-07 20:58:57	FaceBoom	Mac	United States	2019-05-07	2019-05-01	True	1.088172
1	4919697	2019-07-09 12:46:07	FaceBoom	iPhone	United States	2019-07-09	2019-07-01	False	1.107237
2	6085896	2019-10-01 09:58:33	organic	iPhone	France	2019-10-01	2019-10-01	False	0.000000
3	22593348	2019-08-22 21:35:48	AdNonSense	PC	Germany	2019-08-22	2019-08-01	False	0.988235
4	31989216	2019-10-02 00:07:44	YRabbit	iPhone	United States	2019-10-02	2019-10-01	False	0.230769
...	...	...	...	...	...	...	...	...	...
150003	999956196527	2019-09-28 08:33:02	TipTop	iPhone	United States	2019-09-28	2019-09-01	False	3.500000
150004	999975439887	2019-10-21 00:35:17	organic	PC	UK	2019-10-21	2019-10-01	False	0.000000
150005	999976332130	2019-07-23 02:57:06	TipTop	iPhone	United States	2019-07-23	2019-07-01	False	2.600000
150006	999979924135	2019-09-28 21:28:09	MediaTornado	PC	United States	2019-09-28	2019-09-01	False	0.205714
150007	999999563947	2019-10-18 19:57:25	organic	iPhone	United States	2019-10-18	2019-10-01	False	0.000000

150008 rows × 9 columns

*Вывод:* профили пользователей составлены, теперь можем приступать к анализу.

Выясним, из каких стран пользователи приходят в приложение и на какую страну приходится больше всего платящих пользователей. Построим таблицу, отражающую количество пользователей и долю платящих из каждой страны.

# график для всех уникальных пользователей
ax1 = profiles.pivot_table(index='region', values='user_id', aggfunc='nunique') \
    .sort_values(by='user_id', ascending=False) \
    .plot(kind='bar', figsize=(5, 3), title="Уникальные пользователи в разбивке по регионам", legend=False)
ax1.set_xlabel('Регион')
ax1.set_ylabel('Количество пользователей')
ax1.set_xticklabels(ax1.get_xticklabels(), rotation=45);

# график для платящих уникальных пользователей
ax2 = profiles.query('payer==True').pivot_table(index='region', values='user_id', aggfunc='nunique') \
    .sort_values(by='user_id', ascending=False) \
    .plot(kind='bar', figsize=(5, 3), title="Уникальные платящие пользователи в разбивке по регионам", legend=False)
ax2.set_xlabel('Регион')
ax2.set_ylabel('Количество пользователей')
ax2.set_xticklabels(ax2.get_xticklabels(), rotation=45);

# таблица, отражающая количество пользователей и долю платящих из каждой страны

df_all = profiles.groupby(['region']).agg({'user_id': 'nunique'})
df_all.columns = ['nunique_all']

df_payers = profiles.query('payer==True').groupby(['region']).agg({'user_id': 'nunique'})
df_payers.columns = ['nunique_payers']

df_all = df_all.merge(df_payers, on=['region'], how='left')
df_all['payers_share, %'] = ( df_all['nunique_payers'] / df_all['nunique_all'] * 100 ).round(2)

df_all.sort_values(by='payers_share, %', ascending=False)

	nunique_all	nunique_payers	payers_share, %
region
United States	100002	6902	6.90
Germany	14981	616	4.11
UK	17575	700	3.98
France	17450	663	3.80

*Выводы*

• пользователи приходят из четырех стран: США, Великобритания, Франция и Германия
• больше всего пользователей приходят из США (около 100 тыс.)
• доля платящих пользователей также выше в США (6.9%) по сравнению с остальными странами

Проанализируем, какими устройствами пользуются клиенты и какие устройства предпочитают платящие пользователи. Построим таблицу, отражающую количество пользователей и долю платящих для каждого устройства.

# график для всех уникальных пользователей
ax3 = profiles.pivot_table(index='device', values='user_id', aggfunc='nunique') \
    .sort_values(by='user_id', ascending=False) \
    .plot(kind='bar', figsize=(5, 3), title="Уникальные пользователи в разбивке по устройствам", legend=False)
ax3.set_xlabel('Устройство')
ax3.set_ylabel('Количество пользователей')
ax3.set_xticklabels(ax3.get_xticklabels(), rotation=45);

# график для платящих уникальных пользователей
ax4 = profiles.query('payer==True').pivot_table(index='device', values='user_id', aggfunc='nunique') \
    .sort_values(by='user_id', ascending=False) \
    .plot(kind='bar', figsize=(5, 3), title="Уникальные платящие пользователи в разбивке по устройствам", legend=False)
ax4.set_xlabel('Устройство')
ax4.set_ylabel('Количество пользователей')
ax4.set_xticklabels(ax4.get_xticklabels(), rotation=45);

# таблица, отражающая количество пользователей и долю платящих для каждого устройства

df_all_d = profiles.groupby(['device']).agg({'user_id': 'nunique'})
df_all_d.columns = ['nunique_all']

df_payers_d = profiles.query('payer==True').groupby(['device']).agg({'user_id': 'nunique'})
df_payers_d.columns = ['nunique_payers']

df_all_d = df_all_d.merge(df_payers_d, on=['device'], how='left')
df_all_d['payers_share, %'] = ( df_all_d['nunique_payers'] / df_all_d['nunique_all'] * 100 ).round(2)

df_all_d.sort_values(by='payers_share, %', ascending=False)

	nunique_all	nunique_payers	payers_share, %
device
Mac	30042	1912	6.36
iPhone	54479	3382	6.21
Android	35032	2050	5.85
PC	30455	1537	5.05

*Выводы*

• пользователи используют четыре вида устройства: Mac, iPhone, PC и Android
• больше всего пользователей используют iPhone (около 55 тыс.), больше всего платящих пользователей также заходят в приложение из iPhone (около 3 тыс.)
• доля платящих выше у пользователей использующих Mac (6.36%), меньше всего предпочитают платить пользователи PC (5.05%)

Изучим рекламные источники привлечения и определите канам, из которых пришло больше всего платящих пользователей. Построим таблицу, отражающую количество пользователей и долю платящих для каждого канала привлечения.

# график для всех уникальных пользователей
ax5 = profiles.pivot_table(index='channel', values='user_id', aggfunc='nunique') \
    .sort_values(by='user_id', ascending=False) \
    .plot(kind='bar', figsize=(5, 3), title="Уникальные пользователи в разбивке по каналам привлечения", legend=False)
ax5.set_xlabel('Канал привлечения')
ax5.set_ylabel('Количество пользователей')
ax5.set_xticklabels(ax5.get_xticklabels(), rotation=90);

# график для платящих уникальных пользователей
ax6 = profiles.query('payer==True').pivot_table(index='channel', values='user_id', aggfunc='nunique') \
    .sort_values(by='user_id', ascending=False) \
    .plot(kind='bar', figsize=(5, 3), title="Уникальные платящие пользователи в разбивке по каналам привлечения", legend=False)
ax6.set_xlabel('Канал привлечения')
ax6.set_ylabel('Количество пользователей')
ax6.set_xticklabels(ax6.get_xticklabels(), rotation=90);

# таблица, отражающая количество пользователей и долю платящих для каждого канала привлечения

df_all_c = profiles.groupby(['channel']).agg({'user_id': 'nunique'})
df_all_c.columns = ['nunique_all']

df_payers_c = profiles.query('payer==True').groupby(['channel']).agg({'user_id': 'nunique'})
df_payers_c.columns = ['nunique_payers']

df_all_c = df_all_c.merge(df_payers_c, on=['channel'], how='left')
df_all_c['payers_share, %'] = ( df_all_c['nunique_payers'] / df_all_c['nunique_all'] * 100 ).round(2)

df_all_c.sort_values(by='payers_share, %', ascending=False)

	nunique_all	nunique_payers	payers_share, %
channel
FaceBoom	29144	3557	12.20
AdNonSense	3880	440	11.34
lambdaMediaAds	2149	225	10.47
TipTop	19561	1878	9.60
RocketSuperAds	4448	352	7.91
WahooNetBanner	8553	453	5.30
YRabbit	4312	165	3.83
MediaTornado	4364	156	3.57
LeapBob	8553	262	3.06
OppleCreativeMedia	8605	233	2.71
organic	56439	1160	2.06

*Выводы*

• больше всего пользователей пришли на приложение сами (около 56 тыс.), но доля платящих у таких пользователей самая низкая (2.06%)
• больше всего платящих покупателей пришли из канала FaceBoom (около 3.5 тыс.), доля платящих у этого канала также самая высокая (12.2%)

Шаг 4. Маркетинг

Посчитаем общую сумму расходов на маркетинг, проанализируем его распределение по рекламным источникам

print('Общая сумма расходов на маркетинг составила', int(costs['costs'].sum()), 'единиц.')

ax7 = costs.pivot_table(index='channel', values='costs', aggfunc='sum') \
    .sort_values(by='costs', ascending=False) \
    .plot(kind='bar', figsize=(5, 3), title="Распределение расходов на маркетинг по рекламным источникам", legend=False)
ax7.set_xlabel('Канал привлечения')
ax7.set_ylabel('Сумма расходов')
ax7.set_xticklabels(ax7.get_xticklabels(), rotation=90);

Общая сумма расходов на маркетинг составила 105497 единиц.

*Выводы*

• общая сумма расходов на маркетинг составила 105 тыс. единиц
• больше всего денег постратили на TipTop (более 50 тыс.) и FaceBoom (более 30 тыс.)

Построим визуализацию динамики изменения общих расходов во времени (по неделям и месяцам) по каждому источнику

Выше мы выявили, что выделяются два источника рекламы по сумме расходов - TipTop (более 50 тыс.) и FaceBoom (более 30 тыс.), у остальных источников сумма расходов ниже 10 тыс. Для сохранения наглядности, построим два графика динамики расходов: в одном укажем динамику расходов для TipTop и FaceBoom, в другом - для остальных источников.

# график изменения расходов по неделям
costs['week'] = pd.to_datetime(costs['dt']).dt.dayofweek

costs.pivot_table(index='week', values='costs', aggfunc='sum').plot(grid=True, figsize=(15, 5))
plt.xlabel('Номер недели')
plt.title('Динамика изменения общих расходов по дням недели')
plt.show()

c_weekly_main = costs.query('channel=="TipTop" or channel=="FaceBoom"'). \
    pivot_table(index='week', columns='channel', values='costs', aggfunc='sum')
c_weekly_other = costs.query('channel!="TipTop" and channel!="FaceBoom"'). \
    pivot_table(index='week', columns='channel', values='costs', aggfunc='sum')

c_weekly_main.plot(grid=True, figsize=(15, 5))
plt.xlabel('Номер дня недели')
plt.title('Динамика изменения общих расходов по дням недели для TipTop и FaceBoom')
plt.show()

c_weekly_other.plot(grid=True, figsize=(15, 5))
plt.xlabel('Номер дня недели')
plt.title('Динамика изменения общих расходов по дням недели для прочих источников')
plt.show()

# график изменения расходов по месяцам
costs['month'] = pd.to_datetime(costs['dt']).dt.month

costs.pivot_table(index='month', values='costs', aggfunc='sum').plot(grid=True, figsize=(15, 5))
plt.xlabel('Номер месяца')
plt.title('Динамика изменения общих расходов по месяцам')
plt.show()

c_monthly_main = costs.query('channel=="TipTop" or channel=="FaceBoom"'). \
    pivot_table(index='month', columns='channel', values='costs', aggfunc='sum')
c_monthly_other = costs.query('channel!="TipTop" and channel!="FaceBoom"'). \
    pivot_table(index='month', columns='channel', values='costs', aggfunc='sum')

c_monthly_main.plot(grid=True, figsize=(15, 5))
plt.xlabel('Номер месяца')
plt.title('Динамика изменения общих расходов по месяцам для TipTop и FaceBoom')
plt.show()

c_monthly_other.plot(grid=True, figsize=(15, 5))
plt.xlabel('Номер месяца')
plt.title('Динамика изменения общих расходов по месяцам для прочих источников')
plt.show()

*Выводы*

• расходы на рекламу обычно выше по субботам и воскресеньям по всем источникам
• с мая 2019 года наблюдается тенденция к увеличению ежемесячных расходов на рекламу: с 10 тыс. единиц в мае до около 20 тыс. в октябре
• тенденция к увеличению ежемесячных расходов на рекламу также видно у двух основных каналов рекламы: сумма ежемесячных расходов на рекламу в TipTop и FaceBoom увеличилась более чем в два раза с мая 2019 года

Узнаем, сколько в среднем стоило привлечение одного пользователя (CAC) из каждого источника

profiles.pivot_table(index='channel', values='acquisition_cost', aggfunc='mean').round(2). \
                                                            sort_values(by='acquisition_cost', ascending=False)

	acquisition_cost
channel
TipTop	2.80
FaceBoom	1.11
AdNonSense	1.01
lambdaMediaAds	0.72
WahooNetBanner	0.60
RocketSuperAds	0.41
OppleCreativeMedia	0.25
MediaTornado	0.22
YRabbit	0.22
LeapBob	0.21
organic	0.00

*Вывод*

Средний расход на привлечение одного пользователя через TipTop многократно превышает средний расход на привлечение через остальные каналы рекламы. При этом, как было выявлено выше, количество платящих пользователей и доля платящих у TipTop не сильно отличается от остальных (см. payers_table). Попробуем оценить окупаемость рекламы в этих каналах.

Шаг 5. Оценим окупаемость рекламы

Исключим органических пользователей при анализе окупаемости рекламы

Затраты на привлечение органических пользователей нулевые, поэтому они всегда окупаются. Исключим эти источники из нашего анализа.

profiles = profiles.query('channel!="organic"')

Используя графики LTV, ROI и CAC, проанализируем окупаемость рекламы

Считаем, что на календаре 1 ноября 2019 года, но так как у нас данные имеются только до 27 октября 2019, observation_date = 2019-10-27. В бизнес-плане заложено, что пользователи должны окупаться не позднее чем через две недели после привлечения, поэтому horizon_days = 14.

observation_date = datetime(2019, 10, 27).date()  # момент анализа
horizon_days = 14  # горизонт анализа

Для начала оценим общую ситуацию — посмотрим на окупаемость рекламы. Рассчитаем и визуализируем LTV и ROI, вызвав функции get_ltv() и plot_ltv_roi().

# считаем LTV и ROI
ltv_raw, ltv_grouped, ltv_history, roi_grouped, roi_history = get_ltv(
    profiles, orders, observation_date, horizon_days)

# строим графики
plot_ltv_roi(ltv_grouped, ltv_history, roi_grouped, roi_history, horizon_days)

*Выводы*

• в динамике LTV отсутствует отрицательная тенденция (скорее всего, проблема не в конверсии и не в удержании)
• заметна неблагоприятная тенденция в динамике стоимости привлечения пользователей (рост), вследствие чего динамика ROI пользователей на 14-й день устойчиво снижается
• реклама не окупается, ROI для всех лайфтаймов меньше единицы

Проверим конверсию и удержание пользователей и динамику её изменения

# смотрим конверсию

conversion_raw, conversion_grouped, conversion_history = get_conversion(
    profiles, orders, observation_date, horizon_days, dimensions=[]
)

plot_conversion(conversion_grouped, conversion_history, horizon_days)

# смотрим удержание

retention_raw, retention_grouped, retention_history = get_retention(
    profiles, visits, observation_date, horizon_days, dimensions=[]
)

plot_retention(retention_grouped, retention_history, horizon_days)

*Выводы*

• в динамике конверсии на 14-й день отсутствует отрицательная тенденция
• динамика удержания всех платящих и неплатящих пользователей стабильна
• в графиках удержания и конверсии по лайфтаймам аномалии отсутствуют
• следовательно, снижение ROI не связано с проблемами в конверсии и удержании

Проанализируем окупаемость рекламы с разбивкой по устройствам. Построим графики LTV и ROI, а также графики динамики LTV, CAC и ROI.

# смотрим окупаемость с разбивкой по устройствам

dimensions = ['device']

ltv_raw, ltv_grouped, ltv_history, roi_grouped, roi_history = get_ltv(
    profiles, orders, observation_date, horizon_days, dimensions=dimensions
)

plot_ltv_roi(
    ltv_grouped, ltv_history, roi_grouped, roi_history, horizon_days, window=14
)

*Выводы*

• LTV и динамика LTV пользователей с разными устройствами особо не различаются
• стоимость привлечения пользователей с PC и Android ниже, чем у остальных устройств, вследствие чего их ROI также выше
• реклама за весь период для пользователей окупается только у PC на 14-й лайфтайм (график ROI), но во всех устройствах наблюдается отрицательная динамика ROI и в сентябре-октябре реклама для всех них убыточна (график динамики ROI)

Проанализируем окупаемость рекламы с разбивкой по странам. Построим графики LTV и ROI, а также графики динамики LTV, CAC и ROI.

# смотрим окупаемость с разбивкой по странам

dimensions = ['region']

ltv_raw, ltv_grouped, ltv_history, roi_grouped, roi_history = get_ltv(
    profiles, orders, observation_date, horizon_days, dimensions=dimensions
)

plot_ltv_roi(
    ltv_grouped, ltv_history, roi_grouped, roi_history, horizon_days, window=14
)

*Выводы*

• LTV у пользователей из США выше, чем у остальных стран, а динамика LTV в наблюдаемом периоде относительно стабильна
• в динамике стоимости привлечения пользователей из США наблюдается сильный неблагоприятный рост начиная с июня 2019, вследствие чего реклама пользователей из США не окупается
• в динамике стоимости привлечения пользователей из остальных стран (кроме США), наоборот, наблюдается снижение расходов с июня 2019

Проанализируем окупаемость рекламы с разбивкой по рекламным каналам. Построим графики LTV и ROI, а также графики динамики LTV, CAC и ROI.

Компания использует для привлечения пользователей 10 разных каналов. Для сохранения наглядности в графиках, поделим их поровну на две группы по количеству пользователей.

# создаем значения для фильтра
channels_main = df_all_c.sort_values(by='nunique_all', ascending=False).head(5).index.values

# фильтруем
profiles_main = profiles.query('channel in @channels_main')
profiles_other = profiles.query('channel not in @channels_main')

Графики для основной группы каналов

# смотрим окупаемость с разбивкой по источникам привлечения

dimensions = ['channel']

ltv_raw, ltv_grouped, ltv_history, roi_grouped, roi_history = get_ltv(
    profiles_main, orders, observation_date, horizon_days, dimensions=dimensions
)

plot_ltv_roi(
    ltv_grouped, ltv_history, roi_grouped, roi_history, horizon_days, window=14
)

Графики для остальной группы каналов

# смотрим окупаемость с разбивкой по источникам привлечения

dimensions = ['channel']

ltv_raw, ltv_grouped, ltv_history, roi_grouped, roi_history = get_ltv(
    profiles_other, orders, observation_date, horizon_days, dimensions=dimensions
)

plot_ltv_roi(
    ltv_grouped, ltv_history, roi_grouped, roi_history, horizon_days, window=14
)

*Выводы*

• у трех каналов ROI меньше единицы и реклама не окупается: TipTop, FaceBoom, AdNonSense
• у TipTop имеется четко выраженная отрицательная динамика в стоимости привлечения пользователей, при этом рост стоимости привлечения пользователей не привел к положительной динамике в LTV
• ROI четырех каналов выше уровня 2 - lambdaMediaAds, LeapBob, YRabbit, MediaTornado, RocketSuperAds, рекламному отделу стоит проанализировать возможность увеличения рекламы в этих каналах

Проанализируем конверсию и удержание пользователей в разбивке по каналам привлечения

Графики для основной группы каналов

# смотрим конверсию с разбивкой по устройствам

conversion_raw, conversion_grouped, conversion_history = get_conversion(
    profiles_main, orders, observation_date, horizon_days, dimensions=dimensions
)

plot_conversion(conversion_grouped, conversion_history, horizon_days) 

# смотрим удержание с разбивкой по устройствам

retention_raw, retention_grouped, retention_history = get_retention(
    profiles_main, visits, observation_date, horizon_days, dimensions=dimensions
)

plot_retention(retention_grouped, retention_history, horizon_days)

Графики для остальной группы каналов

# смотрим конверсию с разбивкой по устройствам

conversion_raw, conversion_grouped, conversion_history = get_conversion(
    profiles_other, orders, observation_date, horizon_days, dimensions=dimensions
)

plot_conversion(conversion_grouped, conversion_history, horizon_days) 

# смотрим удержание с разбивкой по устройствам

retention_raw, retention_grouped, retention_history = get_retention(
    profiles_other, visits, observation_date, horizon_days, dimensions=dimensions
)

plot_retention(retention_grouped, retention_history, horizon_days)

*Вывод*

• самое низкое удержание наблюдается у каналов FaceBoom и AdNonSense, на 14 лайфтайм их удержание почти равно нулю (следовательно, причина не окупаемости их рекламы в том, что они пиводят платящих покупателей с низким качеством, которые быстро уходят)
• самая низкая конверсия среди крупных каналов наблюдается у OppleCreativeMedia и LeapBob (но выше у этих же каналов мы выявили самый высокий ROI на 14-й лайфтайм, следовательно, эти каналы не являются дорогими и количество платящих покупателей с этих каналов окупает себя)

Выводы

Для продвижения развлекательного приложения Procrastinate Pro+ компания за анализируемый период произвела огромные вложения в рекламу, но несмотря на это последние несколько месяцев компания терпит убытки. Как показал анализ, основная причина убытков - неправильно выбранные каналы рекламы.

*Возможные причины обнаруженных проблем*

• компания заметно увеличила расходы на канал TipTop и он стал основным местом публикации рекламы, но при этом средняя стоимость привлечение одного пользователя в этом канале многократно выше, чем в других каналах, а реклама на этом канале не окупается (вывод - это необоснованно дорогой канал)
• скорее всего TipTop был сфокусирован на рекламе для пользователей в США, вследствие чего стоимость привлечение одного пользователя в США также выросла
• реклама не окупается также во втором и в четвертом по величине расходов на рекламу каналах - в FaceBoom и в AdNonSense
• причина не окупаемости рекламы в FaceBoom и в AdNonSense - плохое качество платящих покупателей с этих каналов, которые быстро уходят

*Рекомендации для рекламного отдела*

• три из четырех основных поставщиков рекламы не окупаются, надо подумать о возможности смены этих каналов рекламы
• потенциально хорошими каналами для рекламы являются: lambdaMediaAds, LeapBob, YRabbit, MediaTornado, RocketSuperAds
• доля платящих у пользователей из США самая высокая, поэтому закрывать рекламу в США не рекомендуется несмотря на низкий ROI (низкий ROI м.б. связан с неправльно выбранными каналами рекламы)
• текущая реклама скорее всего была сфокусирована на пользователей из США, рекомендуется также продвигать приложение у пользователей из других регионов, например из Германии, у которого доля платящих составляет 4.11%
• можно повысить удержание для пользователей из США с помощью введения персонализированных UX-стратегий, e-mail рассылок, персональных рекомендаций, push-уведомлений; можно ввести определённые "награды" или привилегии для постоянных пользователей
• для канала FaceBoom можно настроить более таргетированное привлечение, направленное на пользователей с необходимыми характеристиками.